using AutoMapper;
using MediatR;
using RBAC.Domain.MaterialManage;
using RBAC.ErrorCode;
using RBAC.Interstructrue;
using RBAC.Interstructrue.Base;
using RBAC.Write.Api.Application.Command.ERP.MaterialFile;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ClosedXML.Excel;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Http;
using Yitter.IdGenerator;

namespace RBAC.Write.Api.Application.Handler.ERP.MaterialFile
{
    /// <summary>
    /// 导入物料基本档案处理器
    /// </summary>
    public class ImportMaterialHandler : IRequestHandler<ImportMaterialCommand, APIResult<ImportResultDto>>
    {
        private readonly IBaseRepository<Mater> materialRepository;
        private readonly IBaseRepository<MaterType> materTypeRepository;
        private readonly ILogger<ImportMaterialHandler> logger;
        private readonly IMapper mapper;
        private readonly RBACDbContext dbContext;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="materialRepository">物料仓储</param>
        /// <param name="materTypeRepository">物料分类仓储</param>
        /// <param name="logger">日志记录器</param>
        /// <param name="mapper">对象映射器</param>
        /// <param name="dbContext">数据库上下文</param>
        public ImportMaterialHandler(
            IBaseRepository<Mater> materialRepository,
            IBaseRepository<MaterType> materTypeRepository,
            ILogger<ImportMaterialHandler> logger,
            IMapper mapper,
            RBACDbContext dbContext)
        {
            this.materialRepository = materialRepository;
            this.materTypeRepository = materTypeRepository;
            this.logger = logger;
            this.mapper = mapper;
            this.dbContext = dbContext;
        }

        /// <summary>
        /// 处理导入物料基本档案请求
        /// </summary>
        /// <param name="request">导入请求</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>导入结果</returns>
        public async Task<APIResult<ImportResultDto>> Handle(ImportMaterialCommand request, CancellationToken cancellationToken)
        {
            var result = new APIResult<ImportResultDto>
            {
                Data = new ImportResultDto()
            };

            try
            {
                logger.LogInformation("开始导入物料基本档案数据");

                // 验证文件
                if (request.File == null || request.File.Length == 0)
                {
                    result.Code = APIEnums.Error;
                    result.Msg = "请选择有效的Excel文件";
                    return result;
                }

                // 验证文件扩展名
                var extension = Path.GetExtension(request.File.FileName).ToLower();
                if (extension != ".xlsx" && extension != ".xls")
                {
                    result.Code = APIEnums.Error;
                    result.Msg = "请上传Excel文件（.xlsx或.xls格式）";
                    return result;
                }

                // 读取Excel文件
                var importData = await ReadExcelFile(request.File);
                if (importData == null || !importData.Any())
                {
                    result.Code = APIEnums.Error;
                    result.Msg = "Excel文件中没有找到有效数据";
                    return result;
                }

                logger.LogInformation("从Excel中读取到{Count}条数据", importData.Count);

                // 验证和处理数据
                var validationResult = await ValidateAndProcessData(importData);

                result.Data = validationResult;
                result.Code = APIEnums.Success;
                result.Msg = validationResult.FailCount > 0
                    ? $"导入完成，成功{validationResult.SuccessCount}条，失败{validationResult.FailCount}条"
                    : $"导入成功，共{validationResult.SuccessCount}条数据";

                logger.LogInformation("物料基本档案数据导入完成，成功{Success}条，失败{Fail}条",
                    validationResult.SuccessCount, validationResult.FailCount);
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "物料基本档案数据导入失败：{Message}", ex.Message);
                result.Code = APIEnums.Error;
                result.Msg = "导入物料基本档案数据失败：" + ex.Message;
            }

            return result;
        }

        /// <summary>
        /// 读取Excel文件数据
        /// </summary>
        /// <param name="file">Excel文件</param>
        /// <returns>导入数据列表</returns>
        private async Task<List<MaterialImportPreview>> ReadExcelFile(IFormFile file)
        {
            var importData = new List<MaterialImportPreview>();

            using (var stream = new MemoryStream())
            {
                await file.CopyToAsync(stream);
                stream.Position = 0;

                using (var workbook = new XLWorkbook(stream))
                {
                    var worksheet = workbook.Worksheet(1);
                    var rows = worksheet.RangeUsed().RowsUsed().Skip(1); // 跳过表头

                    foreach (var row in rows)
                    {
                        try
                        {
                            var data = new MaterialImportPreview
                            {
                                MaterialCode = row.Cell(1).GetValue<string>()?.Trim() ?? string.Empty,
                                MaterialName = row.Cell(2).GetValue<string>()?.Trim() ?? string.Empty,
                                TypeCode = row.Cell(3).GetValue<string>()?.Trim() ?? string.Empty,
                                ModelSpec = row.Cell(4).GetValue<string>()?.Trim() ?? string.Empty,
                                Unit = row.Cell(5).GetValue<string>()?.Trim() ?? string.Empty,
                                SupplyMode = row.Cell(6).GetValue<string>()?.Trim() ?? string.Empty,
                                DrawingNo = row.Cell(7).GetValue<string>()?.Trim() ?? string.Empty,
                                SpecDesc = row.Cell(8).GetValue<string>()?.Trim() ?? string.Empty,
                                Price = ParseDecimal(row.Cell(9).GetValue<string>()),
                                Status = ParseStatus(row.Cell(10).GetValue<string>()),
                                Remark = row.Cell(11).GetValue<string>()?.Trim() ?? string.Empty
                            };

                            if (!string.IsNullOrEmpty(data.MaterialCode) && !string.IsNullOrEmpty(data.MaterialName))
                            {
                                importData.Add(data);
                            }
                        }
                        catch (Exception ex)
                        {
                            logger.LogWarning("解析Excel行数据失败：{Message}", ex.Message);
                        }
                    }
                }
            }

            return await Task.FromResult(importData);
        }

        /// <summary>
        /// 验证和处理数据
        /// </summary>
        /// <param name="importData">导入数据</param>
        /// <returns>导入结果</returns>
        private Task<ImportResultDto> ValidateAndProcessData(List<MaterialImportPreview> importData)
        {
            var result = new ImportResultDto();
            var rowNumber = 2; // Excel从第2行开始（跳过表头）

            // 获取现有数据用于验证
            var existingMaterials = materialRepository.GetAll();
            var existingMaterialDict = existingMaterials.ToDictionary(x => x.MaterCode ?? string.Empty, x => x);

            var existingTypes = materTypeRepository.GetAll();
            var existingTypeDict = existingTypes.ToDictionary(x => x.TypeCode ?? string.Empty, x => x);

            // 准备批量插入的数据
            var entitiesToInsert = new List<Mater>();

            foreach (var data in importData)
            {
                var errors = new List<string>();

                // 验证必填字段
                if (string.IsNullOrEmpty(data.MaterialCode))
                    errors.Add("物料编码不能为空");
                if (string.IsNullOrEmpty(data.MaterialName))
                    errors.Add("物料名称不能为空");
                if (string.IsNullOrEmpty(data.Unit))
                    errors.Add("计量单位不能为空");

                // 验证编码唯一性
                if (!string.IsNullOrEmpty(data.MaterialCode) && existingMaterialDict.ContainsKey(data.MaterialCode))
                    errors.Add($"物料编码'{data.MaterialCode}'已存在");

                // 验证分类编码
                long? typeId = null;
                if (!string.IsNullOrEmpty(data.TypeCode))
                {
                    if (existingTypeDict.TryGetValue(data.TypeCode, out var type))
                    {
                        typeId = type.Id;
                    }
                    else
                    {
                        errors.Add($"分类编码'{data.TypeCode}'不存在");
                    }
                }

                if (errors.Any())
                {
                    // 记录错误
                    result.FailCount++;
                    result.ErrorDetails.Add(new ImportErrorDetail
                    {
                        RowNumber = rowNumber,
                        ErrorMessage = string.Join("；", errors),
                        OriginalData = new Dictionary<string, string>
                        {
                            { "物料编码", data.MaterialCode },
                            { "物料名称", data.MaterialName },
                            { "分类编码", data.TypeCode },
                            { "规格型号", data.ModelSpec },
                            { "计量单位", data.Unit },
                            { "供应方式", data.SupplyMode },
                            { "图纸号", data.DrawingNo },
                            { "规格描述", data.SpecDesc },
                            { "单价", data.Price?.ToString() ?? "" },
                            { "状态", data.Status == 1 ? "启用" : "禁用" },
                            { "备注", data.Remark }
                        }
                    });
                }
                else
                {
                    // 准备数据 - 包含所有字段
                    var material = new Mater
                        {
                            Id = YitIdHelper.NextId(),
                            MaterCode = data.MaterialCode,
                            MaterName = data.MaterialName,
                            MaterTypeId = typeId,
                            ModelSpec = data.ModelSpec,
                            Unit = data.Unit,
                            SupplyMode = data.SupplyMode,
                            DrawingNo = data.DrawingNo,
                            SpecDesc = data.SpecDesc,
                            Price = data.Price,
                            Status = data.Status,
                            CreateTime = DateTime.Now,
                            CreateUser = "导入用户",
                            IsDeleted = false
                        };

                    entitiesToInsert.Add(material);
                    result.SuccessCount++;
                    result.PreviewData.Add(new MaterTypeImportPreview
                    {
                        TypeCode = data.MaterialCode,
                        TypeName = data.MaterialName,
                        Unit = data.Unit,
                        Status = data.Status,
                        Lenght = 0,
                        SupplyMode = "",
                        ParentCode = null
                    });
                }

                rowNumber++;
            }

            // 批量插入数据
            if (entitiesToInsert.Any())
            {
                materialRepository.CreateBatch(entitiesToInsert);
            }

            return Task.FromResult(result);
        }

        /// <summary>
        /// 解析状态值
        /// </summary>
        /// <param name="statusText">状态文本</param>
        /// <returns>状态值</returns>
        private int ParseStatus(string? statusText)
        {
            if (string.IsNullOrEmpty(statusText)) return 1;
            return statusText.Trim() switch
            {
                "启用" or "启用" or "1" or "是" => 1,
                "禁用" or "禁用" or "0" or "否" => 0,
                _ => 1
            };
        }

        /// <summary>
        /// 解析价格值
        /// </summary>
        /// <param name="priceValue">价格字符串</param>
        /// <returns>价格值</returns>
        private decimal? ParseDecimal(string? priceValue)
        {
            if (string.IsNullOrEmpty(priceValue))
                return null;

            if (decimal.TryParse(priceValue.Trim(), out decimal result))
                return result;

            return null;
        }
    }
}